﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
using Com.LongER.Business.ZHM001.ZHM001DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZHM001
{
    public class CZHM001 : ABussinessBase
    {
        private Log log = Log.GetInstance();

        /// <summary>
        /// 获取下拉框的值
        /// </summary>
        /// <returns></returns>
        public ZHM001DS.M_TONGYONGDataTable GetLists()
        {
            String connectionString = ConnectionStringUtil.GetConnectionString();
            M_TONGYONGTableAdapter ta = new M_TONGYONGTableAdapter(connectionString);

            ZHM001DS.M_TONGYONGDataTable tb = ta.GetData();
            ta.Connection.Close();
            return tb;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZHM001DS.M_USERDataTable GetUSerList(Dictionary<string, string> dicParam)
        {
            string strUSERID = dicParam["USERID"];

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END = (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             U.USERID                                                            ");
            objSqlContent.Append("             ,U.USERID_HANZI                                                     ");
            objSqlContent.Append("             ,B.CODE1_HANZI BUMN                                                 ");
            objSqlContent.Append("             ,Z.CODE1_HANZI ZHIW                                                 ");
            objSqlContent.Append("             ,S.MOBIL1                                                           ");
            objSqlContent.Append("             ,S.MOBIL2                                                           ");
            objSqlContent.Append("             ,S.MAIL_ADDR                                                        ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 U.USERID ASC                                                      ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             M_USER U                                                            ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_STAFF                                                             ");
            objSqlContent.Append("             S                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 U.STAFF_ID = S.STAFF_ID                                         ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             B                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 S.BUMEN = B.KEY1                                                ");
            objSqlContent.Append("             AND B.CODE_CD = 'BUMN'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             Z                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 S.ZHIWEI = Z.KEY1                                               ");
            objSqlContent.Append("             AND Z.CODE_CD = 'ZHIW'                                              ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");
            if (!String.IsNullOrWhiteSpace(strUSERID))
                objSqlContent.Append("             AND U.USERID = @USERID                                          ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZHM001:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //
            if (!String.IsNullOrWhiteSpace(strUSERID))
                cmd.Parameters.Add(getSqlParameter("USERID", SqlDbType.Char, strUSERID));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZHM001DS.M_USERDataTable tb = new ZHM001DS.M_USERDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZHM001DS.M_USERRow dr = tb.NewM_USERRow();
                    //row 1
                    dr.USERID = odr["USERID"].ToString();
                    dr.USERID_HANZI = odr["USERID_HANZI"].ToString();
                    dr.BUMEN = odr["BUMN"].ToString();
                    dr.ZHIWEI = odr["ZHIW"].ToString();
                    dr.MOBIL1 = odr["MOBIL1"].ToString();
                    dr.MOBIL2 = odr["MOBIL2"].ToString();
                    dr.MAIL_ADDR = odr["MAIL_ADDR"].ToString();
                    tb.AddM_USERRow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}